package com.shuai.shuaisecondhand_springboot.mapper;


import com.shuai.shuaisecondhand_springboot.entity.Order;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface OrderMapper {

    //sql->查询
    @Select("SELECT * FROM `order`")
    List<Order> findAll();

    //sql->根据订单Oid查询所有数据(左外连接)
    @Select("SELECT `order`.*,goods.name,goods.sell_username,goods.photo,goods.describe,user.username FROM user,`order` LEFT JOIN goods ON `order`.id=goods.id WHERE `order`.id=#{Oid} AND `order`.buys_id=user.id")
    Order getOneOrder(@Param("Oid") String Oid);

    //sql->根据订单id查询所有数据(数据库)
    @Select("SELECT * FROM `order` WHERE id=#{id}")
    Order findOne(@Param("id") String id);

    //sql->根据买家id查询所有数据
    @Select("SELECT `order`.*,goods.name,goods.sell_username,goods.photo,goods.describe,user.username FROM user,`order` LEFT JOIN goods ON `order`.id=goods.id WHERE (IFNULL(`order`.id,'') LIKE concat('%', #{Oid}, '%')) AND `order`.buys_id=user.id AND `order`.buys_id=#{buyUid}")
    List<Order> findOneBuy(@Param("buyUid") String buyUid,@Param("Oid") String Oid);

    //sql->买家id查询所有数据量
    @Select("SELECT count(*) FROM `order` WHERE (id LIKE concat('%', #{Oid}, '%'))AND `order`.buys_id=#{buyUid}")
    int getBuyTotal(@Param("buyUid") String buyUid,@Param("Oid") String Oid);


    //sql->根据卖家id查询所有数据
    @Select("SELECT `order`.*,goods.name,goods.sell_username,goods.photo,goods.describe,user.username FROM user,`order` LEFT JOIN goods ON `order`.id=goods.id WHERE (IFNULL(`order`.id,'') LIKE concat('%', #{Oid}, '%')) AND `order`.sells_id=user.id AND `order`.sells_id=#{sellUid}")
    List<Order> findOneSell(@Param("sellUid") String sellUid,@Param("Oid") String Oid);

    //sql->卖家id查询所有数据量
    @Select("SELECT count(*) FROM `order` WHERE (`order`.id LIKE concat('%', #{Oid}, '%'))AND `order`.sells_id=#{sellUid}")
    int getSellTotal(@Param("sellUid") String sellUid,@Param("Oid") String Oid);

    //sql->插入
    @Insert("INSERT INTO `order`(buys_id,sells_id,id,price) VALUES (#{BuyUid},#{SellUid},#{id},#{price})")
    Boolean insertOrder(@Param("BuyUid") String BuyUid,@Param("SellUid") String SellUid,@Param("id") String id,@Param("price") String price);

    /*//mybatis的xml方式用sql->修改
    @Update("UPDATE order SET Ausername=#{Ausername},Aavatar=#{Aavatar},Aphone=#{Aphone} WHERE Aid=#{Aid}")
    Boolean updateAdmin(Order order);*/


    //sql->删除
    @Delete("DELETE FROM `order` WHERE id=#{id}")
    Integer deleteOrder(long id);

    //mybatis的xml方式用sql->批量删除
    Integer deleteOrderBatch(List<Integer> ids);

    //sql->分页查询
    @Select("SELECT `order`.*,goods.name,goods.sell_username,goods.photo,goods.describe,user.username FROM user,`order` LEFT JOIN goods ON `order`.id=goods.id WHERE (IFNULL(`order`.id,'') LIKE concat('%', #{Oid}, '%')) AND `order`.buys_id=user.id limit #{pageNum}, #{pageSize}")
    List<Order> getOrderPage(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize, @Param("Oid") String Oid);

    //sql->查询所有数据量
    @Select("SELECT count(*) FROM `order` WHERE (id LIKE concat('%', #{Oid}, '%'))")
    int getTotal(@Param("Oid") String Oid);

    //sql->查询订单总数
    @Select("SELECT COUNT(*) FROM `order`")
    int getAllTotal();

    //sql->查询订单成交价总价格
    @Select("SELECT SUM(price) FROM `order`")
    String getPriceTotal();
}
